package com.css.fxfzfxqh.modules.reportmanagement.service.impl;

import com.css.fxfzfxqh.modules.reportmanagement.service.ExportPGReportDataService;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.Condition;

@Service
public class ExportPGReportDataServiceImpl implements ExportPGReportDataService {
    private static final Logger logger = LoggerFactory.getLogger(ExportPGReportDataServiceImpl.class);
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Override
    public List<Map<String, Object>> getEarthquakeEffects(String influenceVersion) {
        StringBuilder sb = new StringBuilder();
        sb.append("select *,ST_AsText(geom) geom1 from fxqh_setting_seismic_influenceflied     where version_code=?  ");
        return jdbcTemplate.queryForList(sb.toString(),influenceVersion);
    }

    @Override
    public BigDecimal getAreaByGeom(String geomText) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = this.getEPSGFromGeomText(geomText);
        String sql = "SELECT st_area(ST_Transform(st_geometryfromtext(?,4490),"+epsg+"))";

        return jdbcTemplate.queryForObject(sql, BigDecimal.class, geomText);
    }

    @Override
    public List<Map<String, Object>> getEconomicLoss(String taskId,String conditionType) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  province_name,city_name,county_name,\n" +
                "sum(pgcd1) pgcd1,sum(pgcd2) pgcd2,sum(pgcd3) pgcd3,sum(pgcd4) pgcd4,sum(pgcd5) pgcd5 from (\n" +
                "select province_name,city_name,county_name,\n" +
                "case when intensity='6' then correction_value else 0 end pgcd1,\n" +
                "case when intensity='7' then correction_value else 0 end pgcd2,\n" +
                "case when intensity='8' then correction_value else 0 end pgcd3,\n" +
                "case when intensity='9' then correction_value else 0 end pgcd4,\n" +
                "case when intensity='10' then correction_value else 0 end pgcd5\n" +
                "from fxqh_economic_loss_calculation_ed\n" +
                "where assess_id=? and condition_type=?) c\n" +
                " GROUP BY province_name,city_name,county_name");
//        return jdbcTemplate.queryForList(sb.toString(),"fa09c740ed744827903490ee750ce092");
        return jdbcTemplate.queryForList(sb.toString(),taskId,conditionType);
    }

    @Override
    public List<Map<String, Object>> getEconomicLossMethod(String taskId,String conditionType) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  province_name,city_name,county_name,\n" +
                "sum(sgglsz1) sgglsz1,sum(sgglsz2) sgglsz2,sum(sgglsz3) sgglsz3,sum(sgglsz4) sgglsz4 from (\n" +
                "select province_name,city_name,county_name,\n" +
                "case when condition='sgglsz1' then correction_value else 0 end sgglsz1,\n" +
                "case when condition='sgglsz2' then correction_value else 0 end sgglsz2,\n" +
                "case when condition='sgglsz3' then correction_value else 0 end sgglsz3,\n" +
                "case when condition='sgglsz4' then correction_value else 0 end sgglsz4\n" +
                "from fxqh_economic_loss_calculation_ed\n" +
                "where assess_id=? and condition_type=?) c\n" +
                " GROUP BY province_name,city_name,county_name");
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        return jdbcTemplate.queryForList(sb.toString(),taskId,conditionType);
    }

    @Override
    public List<Map<String, Object>> getbuildinProbabilityDamage(String taskId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  province_name,city_name,county_name,\n" +
                "CAST(sum(pgcd2)/count(pgcd2)  as DECIMAL(18,6)) pgcd2,CAST(sum(pgcd3)/count(pgcd3) as DECIMAL(18,6)) pgcd3,CAST(sum(pgcd4)/count(pgcd4) as DECIMAL(18,6)) pgcd4,CAST(sum(pgcd5)/count(pgcd5) as DECIMAL(18,6)) pgcd5 from (\n" +
                "select province_name,city_name,county_name,years,\n" +
                "case when damage_degree='pgcd2' then CAST(correction_value_pt as DECIMAL(18,6)) else 0 end pgcd2,\n" +
                "case when damage_degree='pgcd3' then CAST(correction_value_pt as DECIMAL(18,6)) else 0 end pgcd3,\n" +
                "case when damage_degree='pgcd4' then CAST(correction_value_pt as DECIMAL(18,6)) else 0 end pgcd4,\n" +
                "case when damage_degree='pgcd5' then CAST(correction_value_pt as DECIMAL(18,6)) else 0 end pgcd5\n" +
                "from fxqh_resist_earthquakes_calculation_pd\n" +
                "where assess_id=? ) c\n" +
                "GROUP BY province_name,city_name,county_name");
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        return jdbcTemplate.queryForList(sb.toString(),taskId);
    }

    @Override
    public List<Map<String, Object>> getEconomicLossProbability(String taskId) {
        StringBuilder sb = new StringBuilder();
        sb.append("\n" +
                "select  province_name,city_name,county_name,\n" +
                "sum(sgglsz1) sgglsz1,sum(sgglsz2) sgglsz2,sum(sgglsz3) sgglsz3,sum(sgglsz4) sgglsz4,sum(sgglsz5) sgglsz5  from (\n" +
                "select province_name,city_name,county_name,\n" +
                "case when years='1'  then CAST(correction_value_p1 AS FLOAT) else 0 end sgglsz1,\n" +
                "case when years='10' then CAST(correction_value_p1 AS FLOAT) else 0 end sgglsz2,\n" +
                "case when years='20' then CAST(correction_value_p1 AS FLOAT) else 0 end sgglsz3,\n" +
                "case when years='30' then CAST(correction_value_p1 AS FLOAT) else 0 end sgglsz4,\n" +
                "case when years='50' then CAST(correction_value_p1 AS FLOAT) else 0 end sgglsz5\n" +
                "from fxqh_economic_loss_calculation_pd\n" +
                "where assess_id=?) c\n" +
                "GROUP BY province_name,city_name,county_name");
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        return jdbcTemplate.queryForList(sb.toString(),taskId);
    }
    //动态表头数据生成
    @Override
    public List<Map<String, Object>>  getEconomicLossProbabilityPlus(String taskId,String[] yearSplit) {
        StringBuilder sb = new StringBuilder();
        //String[] yearSplit="1,5,30,50,100".split(",");
        sb.append("select  province_name,city_name,county_name ");
        for (int numSheet = 0; numSheet < yearSplit.length; numSheet++) {
            sb.append(", sum(sgglsz"+numSheet+") sgglsz"+numSheet+" ");
        }
        sb.append( "  from (select province_name,city_name, " );

        for (int numSheet = 0; numSheet < yearSplit.length; numSheet++) {
            sb.append("case when years='"+yearSplit[numSheet]+"' then CAST(correction_value AS FLOAT) else 0 end sgglsz"+numSheet+",");
        }
        sb.append(" county_name from fxqh_economic_loss_calculation_pd  where assess_id=?) c GROUP BY province_name,city_name,county_name");


        System.out.println(sb.toString());
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        return jdbcTemplate.queryForList(sb.toString(),taskId);
    }

    @Override
    public List<Map<String, Object>> getbuildinDamage1(String taskId,String condition) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  province_name,city_name,county_name,condition,\n" +
                "sum(correction_value*area) as calculated_value, damage_degree,intensity from  fxqh_resist_earthquakes_calculation_ed\n" +
                "where assess_id=? ") ;
        if(StringUtils.isNotBlank(condition)){
            if("0".equals(condition)){
                sb.append("  and condition in ('ld1','ld2','ld3','ld4','ld5') ");
            }else{
                sb.append("  and version=? ");
            }

        }
        sb.append( "  GROUP BY condition ,damage_degree,province_name,city_name,county_name,intensity order by province_name,city_name,county_name,condition,intensity ");
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        if(StringUtils.isNotBlank(condition)){
            if("0".equals(condition)){
                return jdbcTemplate.queryForList(sb.toString(),taskId);
            }else{
                return jdbcTemplate.queryForList(sb.toString(),taskId, condition);
            }
        }
        return jdbcTemplate.queryForList(sb.toString(),taskId);
    }
    @Override
    public List<Map<String, Object>> getbuildinDamage(String taskId,String condition) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  province_name,city_name,county_name,condition,intensity,\n" +
                "sum(pgcd1) pgcd1,sum(pgcd2) pgcd2,sum(pgcd3) pgcd3,sum(pgcd4) pgcd4,sum(pgcd5) pgcd5 from (\n" +
                "select province_name,city_name,county_name,condition,intensity,\n" +
                "case when damage_degree='pgcd1' then case correction_value when 9999.999900 then 0 else CAST(correction_value*area as DECIMAL(18,2)) end end pgcd1,\n" +
                "case when damage_degree='pgcd2' then case correction_value when 9999.999900 then 0 else CAST(correction_value*area as DECIMAL(18,2)) end end pgcd2,\n" +
                "case when damage_degree='pgcd3' then case correction_value when 9999.999900 then 0 else CAST(correction_value*area as DECIMAL(18,2)) end end pgcd3,\n" +
                "case when damage_degree='pgcd4' then case correction_value when 9999.999900 then 0 else CAST(correction_value*area as DECIMAL(18,2)) end end pgcd4,\n" +
                "case when damage_degree='pgcd5' then case correction_value when 9999.999900 then 0 else CAST(correction_value*area as DECIMAL(18,2)) end end pgcd5\n" +
                "from fxqh_resist_earthquakes_calculation_ed\n" +
                "where assess_id=? ") ;
        if(StringUtils.isNotBlank(condition)){
            if("0".equals(condition)){
                sb.append("  and condition in ('ld1','ld2','ld3','ld4','ld5') ");
            }else{
                sb.append("  and version=? ");
            }

        }
        sb.append( ") c\n" +
                " GROUP BY condition ,province_name,city_name,county_name,intensity order by condition,intensity ");
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        if(StringUtils.isNotBlank(condition)){
            if("0".equals(condition)){
                return jdbcTemplate.queryForList(sb.toString(),taskId);
            }else{
                return jdbcTemplate.queryForList(sb.toString(),taskId, condition);
            }
        }
        return jdbcTemplate.queryForList(sb.toString(),taskId);
    }
//    @Override
//    public List<Map<String, Object>> getbuildinDamage(String taskId) {
//        StringBuilder sb = new StringBuilder();
//        sb.append("select  province_name,city_name,county_name,intensity,\n" +
//                "sum(pgcd1) pgcd1,sum(pgcd2) pgcd2,sum(pgcd3) pgcd3,sum(pgcd4) pgcd4,sum(pgcd5) pgcd5 from (\n" +
//                "select province_name,city_name,county_name,intensity,\n" +
//                "case when damage_degree='pgcd1' then correction_value else 0 end pgcd1,\n" +
//                "case when damage_degree='pgcd2' then correction_value else 0 end pgcd2,\n" +
//                "case when damage_degree='pgcd3' then correction_value else 0 end pgcd3,\n" +
//                "case when damage_degree='pgcd4' then correction_value else 0 end pgcd4,\n" +
//                "case when damage_degree='pgcd5' then correction_value else 0 end pgcd5\n" +
//                "from fxqh_resist_earthquakes_calculation_ed\n" +
//                "where assess_id=?  and condition in ('ld1','ld2','ld3','ld4','ld5')") ;
//        sb.append( ") c\n" +
//                " GROUP BY intensity ,province_name,city_name,county_name");
////        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
//        return jdbcTemplate.queryForList(sb.toString(),taskId);
//    }

    @Override
    public List<Map<String, Object>> getbuildinDamageMethod(String taskId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  province_name,city_name,county_name,condition,\n" +
                "sum(pgcd1) pgcd1,sum(pgcd2) pgcd2,sum(pgcd3) pgcd3,sum(pgcd4) pgcd4,sum(pgcd5) pgcd5 from (\n" +
                "select province_name,city_name,county_name,condition,\n" +
                "case when damage_degree='pgcd1' then case correction_value when 9999.999900 then 0 else correction_value end end pgcd1,\n" +
                "case when damage_degree='pgcd2' then case correction_value when 9999.999900 then 0 else correction_value end end pgcd2,\n" +
                "case when damage_degree='pgcd3' then case correction_value when 9999.999900 then 0 else correction_value end end pgcd3,\n" +
                "case when damage_degree='pgcd4' then case correction_value when 9999.999900 then 0 else correction_value end end pgcd4,\n" +
                "case when damage_degree='pgcd5' then case correction_value when 9999.999900 then 0 else correction_value end end pgcd5\n" +
                "from fxqh_resist_earthquakes_calculation_ed\n" +
                "where assess_id=? and condition in ('sgglsz1','sgglsz2','sgglsz3','sgglsz4') ) c\n" +
                " GROUP BY condition ,province_name,city_name,county_name");
//        return jdbcTemplate.queryForList(sb.toString(),"aba552c12083408db827b67eeb23183d");
        return jdbcTemplate.queryForList(sb.toString(),taskId);
    }

    //根据空间数据wkt获取投影坐标EPSG
    public int getEPSGFromGeomText(String geomText) {
        int epsg = 0;
        if (PlatformObjectUtils.isNotEmpty(geomText)) {
            //获取区域范围extent经纬度
            List<Double> lonlatList = getGeomExtentLonLat(geomText);
            if (lonlatList.size() == 4) {
                double minLon = lonlatList.get(0);//最小经度
                double minLat = lonlatList.get(1);//最小纬度
                double maxLon = lonlatList.get(2);//最大经度
                double maxLat = lonlatList.get(3);//最大纬度
                double temp = (maxLon - minLon) / 3;
                int minEpsg = 0;
                int maxEpsg = 0;
                int type = 6;//投影带类型3度带|6度带
                if (temp > 1) {//根据经度获取投影坐标--6度带
                    type = 6;
                    minEpsg = getEPSGFromLon6(minLon);
                    maxEpsg = getEPSGFromLon6(maxLon);
                } else {//根据经度获取投影坐标--3度带
                    type = 3;
                    minEpsg = getEPSGFromLon3(minLon);
                    maxEpsg = getEPSGFromLon3(maxLon);
                }
                if (minEpsg > 0 && maxEpsg > 0) {
                    if (minEpsg == maxEpsg) {//同一投影带
                        epsg = minEpsg;
                    } else {//不同投影带
                        //获取中心点所在的投影带
                        List<String> centerLonLat = getCenterLonLat(geomText);
                        if (centerLonLat.size() == 2) {
                            //中心点经度
                            double centerLon = Double.valueOf(centerLonLat.get(0));
                            //根据经度获取投影坐标--6度带
                            epsg = getEPSGFromLon6(centerLon);
                        }
                    }
                }
            }
        }

        return epsg;
    }

    //获取区域范围extent经纬度
    public List<Double> getGeomExtentLonLat(String geomText) {
        List<Double> lonlatList = new ArrayList<>();
        String sql = "select st_extent(st_geomfromtext(?,4490))";
        String boxExtent = jdbcTemplate.queryForObject(sql, String.class, geomText);
        if (PlatformObjectUtils.isNotEmpty(boxExtent) && boxExtent.indexOf("BOX(") == 0) {
            String box = boxExtent.replace("BOX(", "").replace(")", "");
            String[] lonlatArr = box.split(",");
            String[] minLonLat = lonlatArr[0].split(" ");
            String[] maxLonLat = lonlatArr[1].split(" ");
            if (minLonLat.length == 2 && maxLonLat.length == 2) {
                lonlatList.add(Double.valueOf(minLonLat[0]));//最小经度
                lonlatList.add(Double.valueOf(minLonLat[1]));//最小纬度
                lonlatList.add(Double.valueOf(maxLonLat[0]));//最大经度
                lonlatList.add(Double.valueOf(maxLonLat[1]));//最大纬度
            }
        }

        return lonlatList;
    }

    //获取中心点经纬度
    public List<String> getCenterLonLat(String geomText) {
        List<String> centerLonLat = new ArrayList<>();
        if (PlatformObjectUtils.isNotEmpty(geomText)) {
            String sql = "SELECT st_astext(st_centroid(st_geometryfromtext(?,4490)))";
            String centerPoint = jdbcTemplate.queryForObject(sql, String.class, geomText);
            if (PlatformObjectUtils.isNotEmpty(centerPoint) && centerPoint.indexOf("POINT(") != -1) {
                String lonlatStr = centerPoint.replace("POINT(", "").replace(")", "");
                String[] lonlat = lonlatStr.split(" ");
                if (lonlat.length == 2) {
                    centerLonLat.add(lonlat[0]);//经度
                    centerLonLat.add(lonlat[1]);//纬度
                }
            }
        }

        return centerLonLat;
    }

    //3°带中，中央子午线=带号×3
    //根据经度获取投影坐标--3度带
    public int getEPSGFromLon3(double srcLon) {
        int epsg = 0;
        int index = 0;
        //4534--CGCS2000_3_Degree_GK_CM_75E--中央子午线75E，带号25
        double startLon = 75 - 1.5;
        //4554--CGCS2000_3_Degree_GK_CM_135E--中央子午线135E，带号45
        double endLon = 135 + 1.5;
        boolean bool = true;
        while (bool) {
            if (srcLon >= startLon && srcLon < (startLon + 3)) {
                epsg = 4534 + index;
                bool = false;
            } else if (srcLon < startLon || srcLon > endLon) {
                epsg = 0;
                bool = false;
            } else {
                index++;
                startLon += 3;
            }
        }

        return epsg;
    }

    //6°带中，中央子午线=带号×6-3
    //根据经度获取投影坐标--6度带
    public int getEPSGFromLon6(double srcLon) {
        int epsg = 0;
        int index = 0;
        //4502--CGCS2000_GK_CM_75E--中央子午线75E，带号13
        double startLon = 75 - 3;
        //4512--CGCS2000_GK_CM_135E--中央子午线135E，带号23
        double endLon = 135 + 3;
        boolean bool = true;
        while (bool) {
            if (srcLon >= startLon && srcLon < (startLon + 6)) {
                epsg = 4502 + index;
                bool = false;
            } else if (srcLon < startLon || srcLon > endLon) {
                epsg = 0;
                bool = false;
            } else {
                index++;
                startLon += 6;
            }
        }

        return epsg;
    }

}
